from flask import Flask, request, jsonify, send_from_directory
import pandas as pd
import os
import uuid

app = Flask(__name__)
UPLOAD_FOLDER = 'uploads/'
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)
DOWNLOAD_URL_PREFIX = '/downloads/'


@app.route('/')
def index():
    return send_from_directory('', 'front.html')


@app.route('/upload', methods=['POST'])
def upload_files():
    if 'file1' not in request.files or 'file2' not in request.files:
        return jsonify({'success': False, 'error': 'No files part'})

    file1 = request.files['file1']
    file2 = request.files['file2']
    key_column = request.form['keyColumn']

    # Save files temporarily
    file1_path = os.path.join(UPLOAD_FOLDER, file1.filename)
    file2_path = os.path.join(UPLOAD_FOLDER, file2.filename)
    file1.save(file1_path)
    file2.save(file2_path)

    # Read Excel files
    df1 = pd.read_excel(file1_path)
    df2 = pd.read_excel(file2_path)

    # Convert key column name to column index (assuming A=0, B=1, etc.)
    key_column_index = ord(key_column[0].upper()) - ord('A')

    # Merge dataframes using VLOOKUP-like operation
    df = pd.merge(df1, df2, left_on=df1.columns[key_column_index],
                         right_on=df2.columns[key_column_index], how='left')

    df.drop(columns=[col for col in df.columns if col.endswith('_x')], inplace=True)

    # 重命名后缀为"_y"的列为去掉"_y"后的名称
    df.rename(columns={col: col[:-2] for col in df.columns if col.endswith('_y')}, inplace=True)

    # Generate a unique filename for the result
    result_filename = f'{uuid.uuid4().hex}.xlsx'
    result_path = os.path.join(UPLOAD_FOLDER, result_filename)

    # Save the merged dataframe to Excel
    df.to_excel(result_path, index=False)

    # Create a URL for the downloaded file
    download_url = f'{DOWNLOAD_URL_PREFIX}{result_filename}'

    return jsonify({'success': True, 'url': download_url})


@app.route(DOWNLOAD_URL_PREFIX + '<filename>')
def download_file(filename):
    return send_from_directory(UPLOAD_FOLDER, filename, as_attachment=True)


if __name__ == '__main__':
    app.run(debug=True)